<?php

namespace Unicom\Controller;

use Think\Controller;

class IvrController extends AdminController
{

    public function list_()
    {
        layout(false);
        $model = M('wa.pricecb_result', 'ivr_');

        $pdate_start = I('param.pdate_start', date('Y-m-d'));
        $pdate_end = I('param.pdate_end', date('Y-m-d'));


        $sql = " select *,
        (select count(*) from wa.ivr_pricecb_result where channel=b.channel and cod=200 and sync=1 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as sync_count,
         (select count(*) from wa.ivr_code_param where channel=b.channel  and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as code_count,
	(select count(*) from wa.ivr_code_param where code=200 and channel=b.channel and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as code_success_count,
        (select count(*) from wa.ivr_pricing_param where channel=b.channel and `status`=407 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as up_success_count,
        (select count(*) from wa.ivr_pricecb_result where channel=b.channel and cod=200 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as count
       
        
 from
        (select create_at,channel
        from wa.ivr_pricecb_result as a
        where (FROM_UNIXTIME(a.create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')
        group by channel
        order by create_at desc
      ) as b";

        $sql_all = " select 
        (select count(*) from wa.ivr_pricecb_result where  cod=200 and sync=1 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as sync_count,
         (select count(*) from wa.ivr_code_param where  (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as code_count,
	(select count(*) from wa.ivr_code_param where code=200  and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as code_success_count,
        (select count(*) from wa.ivr_pricing_param where  `status`=407 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as up_success_count,
        (select count(*) from wa.ivr_pricecb_result where  cod=200 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as count
       
        ";
        $data1 = $model->query($sql_all)[0];
        $data = $model->query($sql);
        // var_dump($data1);

        $this->assign('pdate_start', $pdate_start);
        $this->assign('pdate_end', $pdate_end);
        $this->assign('data', $data);
        $this->assign('data1', $data1);

        $this->display();

    }

    public function find_($phone)
    {
        layout(false);
        $model = M('wa.month', 'cp_');
        $today = I('param.today');
        $channel = I('param.channel');

        $sql = "
        select a.*,FROM_UNIXTIME(create_at, '%Y-%m-%d %h:%i:%s') as create_at,p.dcprivence
        from wa.ivr_pricecb_result as a
        left join cp.cp_privence as p on left (a.cal,7)=p.dcmobile
        where cal='$phone'
        
        order by create_at desc 
        ";
        $data = $model->query($sql);


        $this->assign('data', $data);
        $this->display();

    }


    //成功详情

    public function info_($today, $channel)
    {
        layout(false);
        $model = M('wa.month', 'cp_');
        $today = I('param.today');
        $channel = I('param.channel');

        $sql = "
        select a.*,FROM_UNIXTIME(create_at, '%Y-%m-%d %h:%i:%s') as create_at,p.dcprivence
        from wa.ivr_pricecb_result as a
        left join cp.cp_privence as p on left (a.cal,7)=p.dcmobile
        where FROM_UNIXTIME(create_at, '%Y-%m-%d')='$today' and channel='$channel' and cod=200
        
        order by a.create_at desc 
        ";
        $data = $model->query($sql);


        $this->assign('data', $data);
        $this->display();

    }


    //上行不成功详情

    public function upErrorinfo_($today, $channel)
    {
        layout(false);
        $model = M('wa.ivr_pricing_param', '');
        $today = I('param.today');
        $channel = I('param.channel');

        $sql = "
        select a.*,FROM_UNIXTIME(create_at, '%Y-%m-%d %h:%i:%s') as create_at,p.dcprivence
        from wa.ivr_pricing_param as a
        left join cp.cp_privence as p on left (a.ext,7)=p.dcmobile
        where FROM_UNIXTIME(create_at, '%Y-%m-%d')='$today' and channel='$channel'
        
        order by a.create_at desc
        ";
        $data = $model->query($sql);


        $this->assign('data', $data);
        $this->display("upinfo_");

    }

    //上行成功详情

    public function upinfo_($today, $channel)
    {
        layout(false);
        $model = M('wa.ivr_pricing_param', '');
        $today = I('param.today');
        $channel = I('param.channel');

        $sql = "
        select a.*,FROM_UNIXTIME(create_at, '%Y-%m-%d %h:%i:%s') as create_at,p.dcprivence
        from wa.ivr_pricing_param as a
        left join cp.cp_privence as p on left (a.ext,7)=p.dcmobile
        where FROM_UNIXTIME(create_at, '%Y-%m-%d')='$today' and channel='$channel' and `status`=407
        
        order by a.create_at desc
        ";
        $data = $model->query($sql);


        $this->assign('data', $data);
        $this->display("upinfo_");

    }


    public function syncInfo_($today, $channel)
    {
        layout(false);
        $model = M('wa.month', 'cp_');
        $today = I('param.today');
        $channel = I('param.channel');

        $sql = "
        select *,FROM_UNIXTIME(create_at, '%Y-%m-%d %h:%i:%s') as create_at,p.dcprivence
        from wa.ivr_pricecb_result as a 
         left join cp.cp_privence as p on left (a.cal,7)=p.dcmobile
        where FROM_UNIXTIME(create_at, '%Y-%m-%d')='$today' and channel='$channel' and sync=1 and cod=200
        
        order by a.create_at desc 
        ";
        $data = $model->query($sql);


        $this->assign('data', $data);
        $this->display("info_");

    }


    public function index()
    {
        layout(false);
        $this->assign('date', date('Y-m-d'));
        $this->display("privence");
    }


    public function infoByChannel()
    {


        layout(false);
        $pdate_start = I('param.pdate_start', date("Y-m-d"));
        $pdate_end = I('param.pdate_end', date("Y-m-d"));

        $pchannel = I('param.pchannel', 0);

        $this->assign("pdate_start", $pdate_start);
        $this->assign("pdate_end", $pdate_end);
        $this->assign("pchannel", $pchannel);
        $model = M();
        $channels = $model->query("select id as `key` from wa.wa_auth_key where product=6");

        $this->assign("channels", $channels);
        if (IS_GET) {

            $this->display("channel_info");
            return;
        }


        $sql_channel = "select *,
        (select count(*) from wa.ivr_pricecb where channel=a.channel and dcprivence=a.dcprivence and cod=200 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as jifei_count,
        (select count(*) from wa.ivr_code where channel=a.channel and dcprivence=a.dcprivence and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as code_count,
        (select count(*) from wa.ivr_code where channel=a.channel and dcprivence=a.dcprivence and code=200 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as code_success_count
        
        
        
        from 
        (
        select p.dcprivence,a.channel,count(*) as up_count
        
        from wa.ivr_pricing_param as a
        left join cp.cp_privence as p on left (a.cal,7)=p.dcmobile
        
        WHERE a.status=407 and a.channel=$pchannel and  (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')
        group by p.dcprivence
        ) as a";

        $sql_all = "select *,
        (select count(*) from wa.ivr_pricecb where  dcprivence=a.dcprivence and cod=200 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as jifei_count,
        (select count(*) from wa.ivr_code where  dcprivence=a.dcprivence and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as code_count,
        (select count(*) from wa.ivr_code where  dcprivence=a.dcprivence and code=200 and (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')) as code_success_count
        
        
        
        from 
        (
        select p.dcprivence,a.channel,count(*) as up_count
        
        from wa.ivr_pricing_param as a
        left join cp.cp_privence as p on left (a.cal,7)=p.dcmobile
        
        WHERE a.status=407  and  (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')
        group by p.dcprivence
        ) as a";
        $sql = $sql_all;
        if ($pchannel != 0) {
            $sql = $sql_channel;
        }
        $data = $model->query($sql);


        $this->assign("data", $data);
        $this->display("channel_info");


    }


    public function complaint_()
    {
        layout(false);
        $model = M('cp.complaint', 'cp_');

        if (IS_GET) {
            $this->display();

        } else {
            if ($_FILES['cpfile']['error'] == 0) {
                $txt = $_FILES['cpfile']['tmp_name'];

                if ("text/plain" == $_FILES['cpfile']['type']) {

                    $content = file_get_contents($txt);

                    $arr = explode("\n", $content);

                    foreach ($arr as $key => $value) {
                        $where = array('phone' => cutstr_html($value));
                        $item = array('phone' => cutstr_html($value), 'create_at' => time());
                        $ret = $model->where($where)->find();

                        if (!$ret) {
                            $model->add($item);

                        }

                    }

                    $this->success("导入成功");


                } else {
                    $this->error("不支持此类文件上传");
                }
            } else {
                $this->error(showUploadError($_FILES['cpfile']['error']));
            }
        }

    }


    public function complaint_stat()
    {
        layout(false);
        $start = I('param.pdate_start',  date("Y-m-d"));
        $end = I('param.pdate_end',  date("Y-m-d"));
        $tag =I('param.tag',1);
        $model = M();

        $sql = "select b.channel,count(*) as total_count from cp.view_complaint as a
        left join wa.ivr_pricecb_result as b on a.phone=b.cal 
        where b.cod=200 and FROM_UNIXTIME(b.create_at,'%Y-%m-%d') BETWEEN '$start' and '$end'
        group by b.channel order by total_count desc";
        if ($tag==2){
            $start = date("Y-m-d");
            $end = date("Y-m-d");
            $sql = "select b.channel,count(*) as total_count from cp.view_complaint as a
            left join wa.ivr_pricecb_result as b on a.phone=b.cal 
            where b.cod=200 and FROM_UNIXTIME(a.create_at,'%Y-%m-%d') BETWEEN '$start' and '$end'
            group by b.channel order by total_count desc";
        }



        $data = $model->query($sql);

        foreach ($data as &$v){

            $sql_ch="select a.dcprivence,b.channel,count(*) as count from cp.view_complaint as a
            left join wa.ivr_pricecb_result as b on a.phone=b.cal 
            where b.cod=200 and b.channel=$v[channel] and FROM_UNIXTIME(b.create_at,'%Y-%m-%d') BETWEEN '$start' and '$end'
            group by a.dcprivence;";
            if ($tag==2) {
                $start = date("Y-m-d");
                $end = date("Y-m-d");
                $sql_ch="select a.dcprivence,b.channel,count(*) as count from cp.view_complaint as a
                left join wa.ivr_pricecb_result as b on a.phone=b.cal 
                where b.cod=200 and b.channel=$v[channel] and FROM_UNIXTIME(a.create_at,'%Y-%m-%d') BETWEEN '$start' and '$end'
                group by a.dcprivence;";
            }
            $tmp=$model->query($sql_ch);
            $v['info']=$tmp;
        }
       // var_dump($data);
        $this->assign("pdate_start", $start);
        $this->assign("pdate_end", $end);
        $this->assign("tag", $tag);
        $this->assign("tags", array('1'=>'订购时间','2'=>'投诉时间'));
        $this->assign('data',$data);

        $this->display('complaint_');

    }

    public function channelByPrivence(){
        $privence = I('param.privence');
        $pdate_start = I('param.pdate_start', date("Y-m-d"));
        $pdate_end = I('param.pdate_end', date("Y-m-d"));
        $model=M();
        $sql="select p.dcprivence,a.channel,count(*) as jifei_count 
        from wa.ivr_pricecb_result as a
        left join cp.cp_privence as p on left (a.cal,7)=p.dcmobile
        
        WHERE p.dcprivence='$privence' and a.cod=200  and  (FROM_UNIXTIME(create_at, '%Y-%m-%d') BETWEEN '$pdate_start' and '$pdate_end')
        group by a.channel order by jifei_count desc";

        $data = $model->query($sql);

        $this->ajaxReturn($data);
    }

}